package DBLayer;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import ModelLayer.Customer;

import ModelLayer.Invoice;

public class DBInvoice implements IFDBInvoice {
	private Connection con;

	public DBInvoice() {
		con = DBConnection.getInstance().getDBcon();
	}

	@Override
	public Invoice getInvoice(int invoiceNo, boolean retriveAssociation) {
		String wClause = " invoiceNo = '" + invoiceNo + "'";
		return singleWhere(wClause, retriveAssociation);
	}

	@Override
	public int insertInvoice(Invoice invoice) throws Exception {
		int nextId = GetMaxId.getMaxId("Select max(invoiceNo) From Sale") + 1;
		int rc = -1;
		int delivery;
		int cashOnDelivery;
		if (invoice.isDelivery()) {
			delivery = 1;
		} else {
			delivery = 0;
		}
		if (invoice.isCashOnDelivery()) {
			cashOnDelivery = 1;
		} else {
			cashOnDelivery = 0;
		}
		String query = "INSERT INTO invoice(invoiceNo, custId, payDate, totalPrice, isDelivery, isCashOnDelivery) VALUES('"
				+ nextId
				+ "','"
				+ invoice.getCustomer().getId()
				+ "','"
				+ invoice.getPayDate()
				+ "','"
				+ invoice.getTotalPrice()
				+ "','" + delivery + "','" + cashOnDelivery + "')";
		System.out.println("insert : " + query);
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		} catch (SQLException ex) {
			System.out.println("Invoice is not inserted");
			throw new Exception("Invoice is not inserted correct");
		}
		return rc;
	}

	@Override
	public ArrayList<Invoice> getAllInvoice(boolean retriveAssociation) {
		return miscWhere("", retriveAssociation);
	}

	@Override
	public int updateInvoice(Invoice invoice) {
		Invoice invoiceObj = invoice;
		int rc = -1;
		int delivery;
		int cashOnDelivery;
		if (invoice.isDelivery()) {
			delivery = 1;
		} else {
			delivery = 0;
		}
		if (invoice.isCashOnDelivery()) {
			cashOnDelivery = 1;
		} else {
			cashOnDelivery = 0;
		}
		String query = "UPDATE invoice SET" + "custId = '"
				+ invoiceObj.getCustomer().getId() + "'," + "payDate = '"
				+ invoiceObj.getPayDate() + "'," + "totalPrice = '"
				+ invoiceObj.getTotalPrice() + "'," + "isDelivery = '"
				+ invoiceObj.getCustomer().getId() + "',"
				+ "isCashOnDelivery = '" + delivery + "',"
				+ " WHERE invoiceNo = '" + cashOnDelivery + "'";
		System.out.println("Update query:" + query);
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		} catch (Exception e) {
			System.out.println("Update exception in invoice db: " + e);
		}
		return rc;
	}

	private Invoice singleWhere(String wClause, boolean retriveAssociation) {
		ResultSet results;
		Invoice invoice = new Invoice();
		String query = buildQuery(wClause);
		System.out.println(query);
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
			if (results.next()) {
				invoice = buildInvoice(results);
				stmt.close();
				if (retriveAssociation) {
					IFDBCustomer dbCustomer = new DBCustomer();
					Customer customer = dbCustomer.getCustomer(invoice
							.getCustomer().getId(), false);
					invoice.setCustomer(customer);
					System.out.println("Customer is selected");
					// retrieve Sales
					IFDBSale dbSale = new DBSale();
					invoice.setSales(dbSale.getAllSale(false));
					System.out.println("Sales are selected");
				} else {
					invoice = null;
				}
			}
		} catch (Exception e) {
			System.out.println("Query exception: " + e);
		}
		return invoice;
	}

	private ArrayList<Invoice> miscWhere(String wClause,
			boolean retriveAssociation) {
		ResultSet results;
		ArrayList<Invoice> list = new ArrayList<Invoice>();
		String query = buildQuery(wClause);
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
			while (results.next()) {
				Invoice invoice = new Invoice();
				invoice = buildInvoice(results);
				list.add(invoice);
			}
			stmt.close();
			if (retriveAssociation) {
				for (Invoice invoice : list) {
					IFDBCustomer dbCustomer = new DBCustomer();
					Customer customer = dbCustomer.getCustomer(invoice
							.getCustomer().getId(), false);
					invoice.setCustomer(customer);
					System.out.println("Customer is selected");

					// retrieve all sales
					IFDBSale dbSale = new DBSale();
					invoice.setSales(dbSale.getAllSale(false));
					System.out.println("Sales are selected");
				}
			}
		} catch (Exception e) {
			System.out.println("Query exception - select: " + e);
			e.printStackTrace();
		}
		return list;
	}

	private Invoice buildInvoice(ResultSet results) {
		Invoice invoice = new Invoice();
		try {
			invoice.setInvoiceNo(results.getInt("invoiceNo"));
			invoice.setCustomer(new Customer(results.getInt("customer_Id")));
			invoice.setPayDate(results.getDate("payDate"));
			invoice.setTotalPrice(results.getDouble("totalPrice"));
			if (results.getInt("isDelivery") == 1) {
				invoice.setDelivery(true);
			} else {
				invoice.setDelivery(false);
			}
			if (results.getInt("isCashOnDelivery") == 1) {
				invoice.setCashOnDelivery(true);
			} else {
				invoice.setCashOnDelivery(false);
			}
		} catch (Exception e) {
			System.out.println("error in building the customer object");
		}
		return invoice;
	}

	private String buildQuery(String wClause) {
		String query = "SELECT invoiceNo, custId, payDate, totalPrice, isDelivery, isCashOnDelivery FROM invoice";
		if (wClause.length() > 0) {
			query = query + " WHERE" + wClause;
		}
		return query;
	}
}
